/**
 * 2010(c) Copyright Oceansoft Information System Co.,LTD. All rights reserved.
 * <p>
 * Compile: JDK 1.6+
 * <p>
 * 版权所有(C)：江苏欧索软件有限公司
 * <p>
 * 公司名称：江苏欧索软件有限公司
 * <p>
 * 公司地址：中国苏州科技城青山路1号
 * <p>
 * 网址: http://www.oceansoft.com.cn
 * <p>
 * 版本: 苏州公安统一用户管理平台1.0
 * <p>
 * 作者: 090922(陈伟)
 * <p>
 * 文件名:SysUserImpl.java
 * <p>
 * 类产生时间: 2012-5-23 下午5:12:18
 * <p>
 * 负责人: 090922(陈伟)
 * <p>
 * Email:javacspring@gmail.com
 * <p>
 * 所在组 : 苏州公安统一用户管理平台
 * <p>
 * 所在部门: 电信/国土——技术二部
 * <p>
 * <p>
 */
package com.oceansoft.core.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import com.hui.platform.framework.dao.AbstractDaoImpl;
import com.oceansoft.core.common.util.DateUtil;
import com.oceansoft.core.common.util.PageUtil;
import com.oceansoft.core.common.util.Pagination;
import com.oceansoft.core.common.util.StringUtil;
import com.oceansoft.core.dao.ISysUserDao;
import com.oceansoft.core.dao.mapper.SysUserMapper;
import com.oceansoft.core.domain.SysDept;
import com.oceansoft.core.domain.SysDutyDict;
import com.oceansoft.core.domain.SysPartTime;
import com.oceansoft.core.domain.SysUser;

/**
 * 人员信息管理接口实现类
 * 
 * @author sunj
 * 
 */
@Repository
public class SysUserDaoImpl extends AbstractDaoImpl<SysUser> implements ISysUserDao {
	@Resource
	private JdbcTemplate sjt;

	/*@Resource(name = "extendJdbcTemplate")
	private JdbcTemplate extjt;
	*/
	private static final String TableName = "T_SYS_USER";  
	private static final String KeyName = "dbid";

	
	@Autowired
	private SimpleJdbcTemplate simple;
	@Autowired 
	private SimpleJdbcInsert insertBean;
	
	@Override
	protected SimpleJdbcInsert getInsertBean() {
		return insertBean;
	}
	
	@Override
	protected SimpleJdbcTemplate getSimpleJdbcTemplate() {
		return simple;
	}
	
	@Override
	protected String getKeyName() {
		return KeyName;
	}

	@Override
	protected String getTableName() {
		return TableName;
	}

	@Override
	protected Class<SysUser> getType() {
		return SysUser.class;
	}

	private String truckZero(String id)
	{
		if(id.endsWith("00"))
		{
			id = id.substring(0,id.length()-2);
			return truckZero(id);
		}
		
		return id;
	}
	
	public Pagination<SysUser> findByPage(String deptId, int pageIndex, SysUser user) {
		StringBuffer sql = new StringBuffer("SELECT T.*,T1.ALIAS DEPTNAME,T1.DEEP FROM T_SYS_USER T LEFT");
		sql.append(" JOIN T_SYS_DEPT T1 ON T.DEPTID=T1.DEPTID WHERE T.DELFLAG='0'");
		
		deptId = truckZero(deptId);
		if ("0".equals(user.getCascade())) {
			// 查询本部门
			if(deptId.indexOf("00")==4)
			{
				if(deptId.length()==8)
				{
					sql.append(" AND T.DEPTID LIKE '").append(deptId).append("%'");
				}
				else
				{
					sql.append(" AND T.DEPTID LIKE '").append(deptId).append("%'");
				}
			}
			else
			{
				if(deptId.length()==6)
				{
					sql.append(" AND T.DEPTID LIKE '").append(deptId).append("%'");
				}
				else
				{
					sql.append(" AND T.DEPTID LIKE '").append(deptId).append("%'");
				}
			}
		} else {
			// 包括子部门
			sql.append(" AND T.DEPTID IN (SELECT DEPTID FROM T_SYS_DEPT T3 START WITH DEPTID='").append(deptId)
					.append("' CONNECT BY PRIOR DEPTID=PARENTID )");
		}
		if (StringUtils.hasLength(user.getPoliceNum())) {
			sql.append(" AND T.POLICENUM LIKE '%").append(user.getPoliceNum()).append("%'");
		}
		if (StringUtils.hasLength(user.getName())) {
			sql.append(" AND T.NAME LIKE '%").append(user.getName()).append("%'");
		}
		sql.append(" ORDER BY T.DEPTID,T.POLICENUM");
		System.out.println(sql.toString());
		String count_sql = PageUtil.getCountSQL(sql.toString());
		int _total = sjt.queryForInt(count_sql);
		//sql.append(" ORDER BY PARENTID, T.ORDERNUM");
		String page_sql = PageUtil.getPageSQL(sql.toString(), pageIndex);
		List<SysUser> list = sjt.query(page_sql, new SysUserMapper());
		return new Pagination<SysUser>(list, _total, pageIndex);

	}

	public int saveAddUser(SysUser user) {
		int dbid =  sjt.queryForInt("select SEQ_SYS_USER.NEXTVAL from dual");
		String sql = "INSERT INTO T_SYS_USER(DBID,DEPTID,POLICENUM,NAME,delflag,orderNum,password,dept2Name,idcard,tel,USERID)"
				+ " VALUES(?,?,?,?,'0',?,?,?,?,?,?)";
		 sjt.update(sql,dbid, user.getDeptId(), user.getPoliceNum(), user.getName(), user.getOrderNum(),
				user.getPassword(), user.getDept2Name(),user.getIdCard(),user.getTel(),user.getUserId());
		 
		 
		 
		 
		 
		 return 1;
		
	}

	public List<SysUser> findByPoliceNum(String policeNum) {
		String sql = "SELECT T.*,T1.ALIAS DEPTNAME FROM T_SYS_USER T LEFT JOIN T_SYS_DEPT T1 ON T.DEPTID=T1.DEPTID WHERE POLICENUM =?";
		return sjt.query(sql, new SysUserMapper(), policeNum);
	}

	public int editUser(SysUser user) {
		String sql = "UPDATE T_SYS_USER SET DEPTID=?,DEPT2NAME=?,NAME=?,idcard=?,tel=?,USERID=? WHERE DBID=?";
		sjt.update(sql, user.getDeptId(), user.getDept2Name(), user.getName(), user.getIdCard(),user.getTel(),user.getUserId(), user.getDbId());
		return 1;
	}

	@Override
	public List<SysUser> findUserByDept(String deptId) {
		String sql = "SELECT T.*,T1.NAME DEPTNAME FROM T_SYS_USER T,T_SYS_DEPT T1 WHERE T.DEPTID = T1.DEPTID AND T.DEPTID = ? AND ACTIVEFLAG =1";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(), deptId);
		return list;
	}

	@Override
	public SysUser findUserByDbId(Long dbId) {
		String sql = "SELECT T1.NAME AS DEPTNAME,T2.* FROM T_SYS_DEPT T1, T_SYS_USER T2 WHERE T1.DEPTID = T2.DEPTID AND  T2.DBID= ?";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(), dbId);
		SysUser sysUser = list.get(0);
		return sysUser;
	}

	@Override
	public List<SysUser> findUserByDbIdAndName(Long dbId, String name, String deptId) {
		String sql = "SELECT T1.NAME AS DEPTNAME,T2.* FROM T_SYS_DEPT T1,T_SYS_USER T2 WHERE T1.DEPTID = T2.DEPTID";
		if (!name.isEmpty()) {
			sql += " AND T2.NAME LIKE " + '\'' + name + '%' + '\'';
		}
		if (!deptId.isEmpty()) {
			sql += " AND T1.DEPTID='" + deptId + "'";
		}
		return sjt.query(sql, new SysUserMapper());
	}

	@Override
	public List<SysUser> findUserByIds(String[] ids) {
		String _ids = "'" + org.apache.commons.lang.StringUtils.join(ids, ",").replace(",", "','") + "'";
		String sql = String
				.format("SELECT T1.*,T2.NAME DEPTNAME FROM T_SYS_USER T1 LEFT JOIN T_SYS_DEPT T2 ON T1.DEPTID=T2.DEPTID WHERE DELFLAG='0' AND POLICENUM IN(%s)",
						_ids.toString());
		return sjt.query(sql, new SysUserMapper());
	}

	@Override
	public List<SysUser> findUserByIds(String ids) {
		String sql = String
				.format("SELECT t.*,d.name deptname FROM T_SYS_USER t,t_sys_dept d WHERE t.deptid = d.deptid and t.DBID IN(%s)",
						ids);
		return sjt.query(sql, new SysUserMapper());
	}

	@Override
	public List<String> findUserByDeptIds(String ids) {
		String sql = "SELECT POLICENUM FROM T_SYS_USER WHERE DEPTID IN(" + ids + ")";
		return sjt.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString(1);
			}
		});
	}

	@Override
	public Pagination<SysUser> findUserByPagination(String deptId, int pageIndex) {
		String sql_list = PageUtil.getPageSQL("SELECT * FROM (SELECT T1.NAME AS DEPTNAME,0 AS PARTTIME, "
				+ "T2.*,T2.DUTYNAME AS PARTTIMEDUTYNAME FROM T_SYS_DEPT T1, T_SYS_USER T2 "
				+ " WHERE T1.DEPTID = T2.DEPTID AND T2.DEPTID =?  "
				+ "AND T2.DELFLAG = 0 UNION ALL SELECT T4.NAME AS DEPTNAME,"
				+ "1 AS ISPARTTIME, T5.*,T6.DUTYNAME AS PARTTIMEDUTYNAME FROM T_SYS_PARTTIME T3,T_SYS_DEPT T4,"
				+ " T_SYS_USER T5 ,T_SYS_DUTY_DICT T6 WHERE T3.PARTTIMEDEPT =T4.DEPTID AND T3.USERID =T5.POLICENUM  "
				+ "AND T3.DUTYID=T6.DUTYID " + "AND T3.PARTTIMEDEPT=?) ORDER BY deptId, ORDERNUM", pageIndex);
		String sql_count = "SELECT COUNT(*) FROM (SELECT T1.NAME AS DEPTNAME,0 AS PARTTIME, "
				+ "T2.*  FROM T_SYS_DEPT T1, T_SYS_USER T2 " + " WHERE T1.DEPTID = T2.DEPTID AND T2.DEPTID =?  "
				+ "AND T2.DELFLAG = 0 UNION ALL SELECT T4.NAME AS DEPTNAME,"
				+ "1 AS ISPARTTIME, T5.* FROM T_SYS_PARTTIME T3,T_SYS_DEPT T4,"
				+ " T_SYS_USER T5 WHERE T3.PARTTIMEDEPT =T4.DEPTID AND T3.USERID =T5.POLICENUM  "
				+ "AND T3.PARTTIMEDEPT=?)";
		int _total = sjt.queryForInt(sql_count, deptId, deptId);
		List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser sysUser = new SysUser();
				SysDept sysDept = new SysDept();
				sysUser.setDbId(rs.getLong("DBID"));
				sysUser.setName(rs.getString("NAME"));
				sysUser.setIdCard(rs.getString("IDCARD"));
				sysUser.setDeptId(rs.getString("DEPTID"));
				sysUser.setPoliceNum(rs.getString("POLICENUM"));
				sysUser.setPassword(rs.getString("PASSWORD"));
				sysUser.setDutyName(rs.getString("DUTYNAME"));
				sysUser.setBirthday(rs.getString("BIRTHDAY"));
				sysUser.setTel(rs.getString("TEL"));
				sysUser.setRemark(rs.getString("REMARK"));
				sysUser.setActiveFlag(rs.getString("ACTIVEFLAG"));
				sysUser.setDeptName(rs.getString("DEPTNAME"));
				sysUser.setParttime(rs.getInt("PARTTIME"));
				sysUser.setPartTimedutyname(rs.getString("PARTTIMEDUTYNAME"));
				sysUser.setDelflag(rs.getString("DELFLAG"));
				sysDept.setName(rs.getString("DEPTNAME"));
//				sysUser.setSysDept(sysDept);
				return sysUser;
			}
		}, deptId, deptId);
		return new Pagination<SysUser>(list, _total, pageIndex);
	}

	@Override
	public Pagination<SysUser> findUserByDept(String deptId, int pageIndex) {
		String sql_list = PageUtil
				.getPageSQL(
						"SELECT NAME,DUTYNAME,TEL_OFFICE,TEL_MOBILE,TEL_SHORT,TEL_HOME,POLICENUM FROM T_SYS_USER WHERE DEPTID=? AND DELFLAG='0'",
						pageIndex);
		String sql_count = "SELECT COUNT(*) FROM T_SYS_USER WHERE DEPTID=?  AND DELFLAG='0'";
		int _total = sjt.queryForInt(sql_count, deptId);
		List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser sysUser = new SysUser();
				SysDept sysDept = new SysDept();
				sysUser.setName(rs.getString("NAME"));
				sysUser.setDutyName(rs.getString("DUTYNAME"));
				sysUser.setTel(rs.getString("TEL"));
//				sysUser.setSysDept(sysDept);
				return sysUser;
			}
		}, deptId);
		return new Pagination<SysUser>(list, _total, pageIndex);
	}

	@Override
	public Pagination<SysUser> findUserContact(String deptId, int pageIndex) {
		String sql = "SELECT NAME,DUTYNAME,POLICENUM,TEL_OFFICE,TEL_HOME,TEL_MOBILE,TEL_SHORT,TEL_INTER,TEL_OUTSIDE FROM T_SYS_USER WHERE DEPTID=? and delflag='0'";
		String sql_list = PageUtil.getPageSQL(sql, pageIndex);
		String count_sql = "SELECT COUNT(POLICENUM) FROM T_SYS_USER WHERE DEPTID=? and delflag='0'";
		int total = sjt.queryForInt(count_sql, deptId);
		List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser user = new SysUser();
				user.setName(rs.getString("name"));
				user.setDutyName(rs.getString("dutyname"));
				user.setPoliceNum(rs.getString("policenum"));
				return user;
			}
		}, deptId);
		return new Pagination<SysUser>(list, total, pageIndex);
	}

	public Pagination<SysUser> findUserDelegate(String deptId, int pageIndex, String name, String manageDept) {
		String sql;
		String count_sql;
		if (org.springframework.util.StringUtils.hasLength(name)) {
			sql = "SELECT U.NAME,U.DUTYNAME,U.POLICENUM,U.DEPTID,D.NAME DEPTNAME FROM T_SYS_USER U,T_SYS_DEPT D WHERE U.name like '%"
					+ name + "%' and U.delflag='0' AND U.DEPTID=D.DEPTID and u.deptId like '" + manageDept + "%'";
			count_sql = "SELECT COUNT(POLICENUM) FROM T_SYS_USER WHERE delflag='0' AND NAME LIKE '%" + name
					+ "%' and deptId like '" + manageDept + "%'";
			;
			;
			String sql_list = PageUtil.getPageSQL(sql, pageIndex);
			int total = sjt.queryForInt(count_sql);
			List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
				@Override
				public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
					SysUser user = new SysUser();
					user.setName(rs.getString("name"));
					user.setDutyName(rs.getString("dutyname"));
					user.setPoliceNum(rs.getString("policenum"));
					user.setDeptId(rs.getString("deptId"));
					user.setDeptName(rs.getString("deptName"));
					return user;
				}
			});
			return new Pagination<SysUser>(list, total, pageIndex);
		} else {
			sql = "SELECT U.NAME,U.DUTYNAME,U.POLICENUM,U.DEPTID,D.NAME DEPTNAME FROM T_SYS_USER U,T_SYS_DEPT D WHERE U.DEPTID=? and U.delflag='0' AND U.DEPTID=D.DEPTID and u.deptId like '"
					+ manageDept + "%'";
			;
			count_sql = "SELECT COUNT(POLICENUM) FROM T_SYS_USER WHERE DEPTID=? and delflag='0' and deptId like '"
					+ manageDept + "%'";
			;
			String sql_list = PageUtil.getPageSQL(sql, pageIndex);
			int total = sjt.queryForInt(count_sql, deptId);
			List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
				@Override
				public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
					SysUser user = new SysUser();
					user.setName(rs.getString("name"));
					user.setDutyName(rs.getString("dutyname"));
					user.setPoliceNum(rs.getString("policenum"));
					user.setDeptId(rs.getString("deptId"));
					user.setDeptName(rs.getString("deptName"));
					return user;
				}
			}, deptId);
			return new Pagination<SysUser>(list, total, pageIndex);
		}
	}

	@Override
	public Pagination<SysUser> findUserByCritirialPagination(Map<String, String> params, int pageIndex) {
		StringBuilder query_sql = new StringBuilder(
				"SELECT T1.NAME AS DEPTNAME,T2.* FROM T_SYS_DEPT T1,T_SYS_USER T2 WHERE T1.DEPTID = T2.DEPTID");
		StringBuilder count_sql = new StringBuilder(
				"SELECT COUNT(*) FROM  T_SYS_DEPT t1,T_SYS_USER T2  WHERE T1.DEPTID = T2.DEPTID");
		StringBuilder sql = new StringBuilder();
		Iterator<Entry<String, String>> iter = params.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, String> entry = (Map.Entry<String, String>) iter.next();
			sql.append(" AND T2.").append(entry.getKey()).append(" LIKE '%").append(entry.getValue()).append("%'");
		}
		sql.append(" AND T2.ACTIVEFLAG =1");
		int _total = sjt.queryForInt(count_sql.append(sql).toString());
		List<SysUser> list = sjt.query(PageUtil.getPageSQL(query_sql.append(sql).toString(), pageIndex),
				new SysUserMapper());
		return new Pagination<SysUser>(list, _total, pageIndex);
	}

	@Override
	public Pagination<SysUser> findUserByCondition(Map<String, String> params, int pageIndex) {
		String sql = "SELECT * FROM T_SYS_USER T WHERE DEPTID='" + params.get("deptId") + "'";
		if (StringUtil.isNotNull(params.get("name"))) {
			sql += " AND NAME LIKE '%" + params.get("name") + "%'";
		}
		if (StringUtil.isNotNull(params.get("ismobile"))) {
			sql += " AND T.TEL_MOBILE IS NOT NULL";
		}
		sql += " AND ACTIVEFLAG =1";
		int _total = sjt.queryForInt("select count(*) from (" + sql + ") ");
		List<SysUser> list = sjt.query(PageUtil.getPageSQL(sql + "  order by ordernum ", pageIndex),
				ParameterizedBeanPropertyRowMapper.newInstance(SysUser.class));
		return new Pagination<SysUser>(list, _total, pageIndex);
	}

	@Override
	public int deleteUserByDbId(String policenum) {
		//原始为逻辑删
		//String sql = "UPDATE  T_SYS_USER SET ACTIVEFLAG =0 , DELFLAG = 1, remark=policenum WHERE POLICENUM=?";
		String sql = "DELETE FROM T_SYS_USER_ROLE WHERE USERID = ?";
		       sjt.update(sql, policenum);
		       sql = "DELETE FROM  T_SYS_USER  WHERE POLICENUM = ?";
		return sjt.update(sql, policenum);
	}

	@Override
	public List<SysUser> findUserInfoByPoliceNum(String policeNum) {
		String sql = "SELECT T1.NAME AS DEPTNAME,T2.NAME AS PARENTNAME,T3.* FROM T_SYS_DEPT T1 ,T_SYS_DEPT T2 ,T_SYS_USER T3 WHERE T1.PARENTID = T2.DEPTID AND T3.DEPTID =T1.DEPTID AND T3.POLICENUM =?";
		List<SysUser> list = sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser sysUser = new SysUser();
				SysDept sysDept = new SysDept();
				sysUser.setDbId(rs.getLong("DBID"));
				sysUser.setName(rs.getString("NAME"));
				sysUser.setIdCard(rs.getString("IDCARD"));
				sysUser.setDeptId(rs.getString("DEPTID"));
				sysUser.setPoliceNum(rs.getString("POLICENUM"));
				sysUser.setPassword(rs.getString("PASSWORD"));
				sysUser.setDutyName(rs.getString("DUTYNAME"));
				sysUser.setBirthday(rs.getString("BIRTHDAY"));
				sysUser.setRemark(rs.getString("REMARK"));
				sysUser.setActiveFlag(rs.getString("ACTIVEFLAG"));
				sysDept.setName(rs.getString("DEPTNAME"));
				sysDept.setParentName(rs.getString("PARENTNAME"));
//				sysUser.setSysDept(sysDept);
				return sysUser;
			}
		}, policeNum);
		return list;
	}

	@Override
	public int updateUserInfo(SysUser sysUser) {
		// String sql =
		// "UPDATE T_SYS_USER SET NAME=?,IDCARD=?,TEL_OFFICE =? , TEL_HOME =? , TEL_MOBILE=? , TEL_SHORT=? , TEL_INTER =? , TEL_OUTSIDE=? ,ACTIVEFLAG=? WHERE POLICENUM=?";
		// return sjt.update(sql, sysUser.getName(), sysUser.getIdCard(),
		// sysUser.getTel_office(), sysUser.getTel_home(),
		// sysUser.getTel_mobile(), sysUser.getTel_short(),
		// sysUser.getTel_inter(), sysUser.getTel_outside(),
		// sysUser.getActiveFlag(), sysUser.getPoliceNum());
		return 1;
	}

	@Override
	public int updateUserTelInfo(SysUser sysUser) {
		// String sql =
		// "UPDATE T_SYS_USER SET TEL_OFFICE =?,TEL_HOME =?,TEL_MOBILE=?,TEL_SHORT=?,TEL_INTER=?,TEL_OUTSIDE=?　WHERE POLICENUM=?";
		// return sjt.update(sql, sysUser.getTel_office(),
		// sysUser.getTel_home(), sysUser.getTel_mobile(),
		// sysUser.getTel_short(), sysUser.getTel_inter(),
		// sysUser.getTel_outside(), sysUser.getPoliceNum());
		return 1;
	}

	@Override
	public int updatePassword(SysUser user) {
		String sql = "UPDATE T_SYS_USER SET PASSWORD = ? WHERE POLICENUM = ? ";
		return sjt.update(sql, user.getPassword(), user.getPoliceNum());
	}

	@Override
	public SysUser findUserByPoliceNum(String policeNum) {
		String sql = "SELECT T1.ALIAS AS DEPTNAME,T1.DEEP,T2.*,(select photo from t_sys_user_photo where policenum = t2.policenum ) photo FROM T_SYS_DEPT T1, T_SYS_USER T2 WHERE T2.DELFLAG = '0' AND T1.DEPTID = T2.DEPTID AND T2.POLICENUM= ?";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(), policeNum);
		if (list.size() != 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	@Override
	public List<SysDutyDict> findAllDutyInfo() {
		String sql = "SELECT * FROM T_SYS_DUTY_DICT ORDER BY DUTYNAME";
		List<SysDutyDict> list = sjt.query(sql, new RowMapper<SysDutyDict>() {
			@Override
			public SysDutyDict mapRow(ResultSet rs, int arg1) throws SQLException {
				SysDutyDict sysDutyDict = new SysDutyDict();
				sysDutyDict.setDbId(rs.getLong("DBID"));
				sysDutyDict.setDutyId(rs.getString("DUTYID"));
				sysDutyDict.setDutyName(rs.getString("DUTYNAME"));
				return sysDutyDict;
			}
		});
		return list;
	}

	@Override
	public void transferUser(String dutyName, String deptId, String dutyId, String policeNum) {
		String sql = "UPDATE T_SYS_USER SET DEPTID =?,DUTYNAME=?,DUTYID=? WHERE POLICENUM =? ";
		sjt.update(sql, deptId, dutyName, deptId, policeNum);
	}

	@Override
	public void updatePartTime(String deptId, String policeNum, String partTimeDeptId, String partTimeDeptName,
			String dutyId, String dutyName) {
		String dateformat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
		Date date = null;
		try {
			date = DateUtil.stringToDate(dateformat, "yyyy-MM-dd hh:mm:ss");
		} catch (ParseException e) {
			e.printStackTrace();
		}
		String sql = "INSERT INTO T_SYS_PARTTIME(DBID,USERID,MARJORDEPT,PARTTIMEDEPT,CREATETIME,DUTYID,DUTYNAME) VALUES(SEQ_SYS_PARTTIME.NEXTVAL,?,?,?,?,?,?)";
		// String sql_dept =
		// "INSERT INTO T_SYS_DEPT_DUTY(DBID,DEPTNAME,DEPTID,DUTYNAME,DUTYID,CREATETIME) VALUES(SEQ_SYS_DEPT_DUTY.NEXTVAL,?,?,?,?,?)";
		sjt.update(sql, policeNum, deptId, partTimeDeptId, date, dutyId, dutyName);
		// sjt.update(sql_dept, partTimeDeptName, partTimeDeptId, dutyName,
		// dutyId, date);
	}

	@Override
	public int findCountByDept(String deptId) {
		String sql = "SELECT COUNT(*) FROM T_SYS_USER WHERE DEPTID=? and delflag='0'";
		return sjt.queryForInt(sql, deptId);
	}

	@Override
	public List<SysPartTime> queryPartTimeInfoByPoliceNum(String policeNum) {
		String sql = "SELECT T2.NAME AS DEPTNAME,T1.DUTYNAME,T1.dbid DUTYID FROM T_SYS_PARTTIME T1,T_SYS_DEPT T2 WHERE T1.PARTTIMEDEPT =T2.DEPTID and T1.USERID=?";
		List<SysPartTime> list = sjt.query(sql, new RowMapper<SysPartTime>() {
			@Override
			public SysPartTime mapRow(ResultSet rs, int arg1) throws SQLException {
				SysPartTime sysPartTime = new SysPartTime();
				sysPartTime.setDutyid(rs.getString("DUTYID"));
				sysPartTime.setDeptname(rs.getString("DEPTNAME"));
				sysPartTime.setDutyname(rs.getString("DUTYNAME"));
				return sysPartTime;
			}
		}, policeNum);
		return list;
	}

	@Override
	public void updateDept(String dbId, String deptId) {
		String sql = "UPDATE T_SYS_USER SET DEPTID = ? WHERE DBID=?";
		sjt.update(sql, deptId, dbId);
	}

	@Override
	public int addUser(String deptId, String policeNum, String name, String dutyId, String dutyName, String idCard,
			String tel_office, String tel_home, String tel_mobile, String tel_short, String tel_inter,
			String tel_outSide, int orderNum) {
		String sql = "INSERT INTO T_SYS_USER(DBID,DEPTID,POLICENUM,NAME,DUTYID,DUTYNAME,IDCARD,TEL_OFFICE,"
				+ "TEL_HOME,TEL_MOBILE,TEL_SHORT,TEL_INTER,TEL_OUTSIDE,ACTIVEFLAG,orderNum) VALUES(SEQ_SYS_USER.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,1,?)";
		return sjt.update(sql, deptId, policeNum, name, dutyId, dutyName, idCard, tel_office, tel_home, tel_mobile,
				tel_short, tel_inter, tel_outSide, orderNum);
	}

	@Override
	public int checkUserLogin(String policenum, String password) {
		String sql = "SELECT T1.NAME AS DEPTNAME,T2.* FROM T_SYS_DEPT T1, T_SYS_USER T2 WHERE T1.DEPTID = T2.DEPTID AND  T2.POLICENUM= ?  AND PASSWORD=?";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(), policenum, password);
		int _total = list.size();
		if (_total > 0) {
			return 1;
		} else {
			return 0;
		}
	}

	@Override
	public List<SysUser> findUsersByAppId(String appId) {
		StringBuffer sql = new StringBuffer("SELECT U.*,D.NAME DEPTNAME FROM T_SYS_USER U,T_SYS_DEPT D WHERE EXISTS");
		sql.append("(SELECT 1 FROM T_SYS_USER_ROLE UR WHERE U.POLICENUM = UR.USERID AND EXISTS (SELECT 1 FROM").append(
				" T_SYS_APP_ROLE AR WHERE AR.APPID=? AND UR.ROLEID=AR.ROLEID)) AND U.DEPTID = D.DEPTID");
		List<SysUser> list = sjt.query(sql.toString(), new SysUserMapper(), appId);
		return list;
	}

	@Override
	public List<SysUser> findAllSyncUser() {
		String sql = "SELECT NAME,POLICENUM,IDCARD,DEPTID,DUTYNAME,ACTIVEFLAG,TEL_OFFICE FROM T_SYS_USER";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(true));
		return list;
	}

	@Override
	public int deletePartTimeInfo(String policenum, String dutyid) {
		String sql = "DELETE FROM T_SYS_PARTTIME WHERE dbid=? and userid=?";// USERID=?
																			// AND
																			// DUTYID=?";
		int result = sjt.update(sql, dutyid, policenum);
		return result;
	}

	@Override
	public List<SysUser> findOnleUserInfoByPoliceNum(String policenum) {
		String sql = "SELECT * FROM T_SYS_USER WHERE POLICENUM =?";
		List<SysUser> list = sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser sysUser = new SysUser();
				sysUser.setDbId(rs.getLong("DBID"));
				sysUser.setName(rs.getString("NAME"));
				sysUser.setIdCard(rs.getString("IDCARD"));
				sysUser.setDeptId(rs.getString("DEPTID"));
				sysUser.setPoliceNum(rs.getString("POLICENUM"));
				sysUser.setPassword(rs.getString("PASSWORD"));
				sysUser.setDutyName(rs.getString("DUTYNAME"));
				sysUser.setBirthday(rs.getString("BIRTHDAY"));
				sysUser.setRemark(rs.getString("REMARK"));
				sysUser.setActiveFlag(rs.getString("ACTIVEFLAG"));
				return sysUser;
			}
		}, policenum);
		return list;
	}

	@Override
	public Pagination<SysUser> queryUserByPagination(String policenum, String name, String deptid, String checkResult,
			int pageIndex, String manageDept) {
		if (!"1".equals(checkResult)) {
			StringBuilder query_sql = new StringBuilder(
					"SELECT T1.NAME AS DEPTNAME,T2.*,T2.DUTYNAME AS PARTTIMEDUTYNAME FROM T_SYS_DEPT T1,T_SYS_USER T2 WHERE t1.deptId like '"
							+ manageDept + "%' and T1.DEPTID = T2.DEPTID");
			StringBuilder count_sql = new StringBuilder(
					"SELECT COUNT(*) FROM  T_SYS_DEPT t1,T_SYS_USER T2  WHERE t1.deptId like '" + manageDept
							+ "%' and T1.DEPTID = T2.DEPTID");
			StringBuilder sql = new StringBuilder();
			if (org.springframework.util.StringUtils.hasLength(policenum)) {
				sql.append(" AND T2.POLICENUM LIKE  UPPER('%" + policenum + "%')");
			}
			if (org.springframework.util.StringUtils.hasLength(name)) {
				sql.append(" AND T2.NAME LIKE  '%" + name + "%'");
			}
			sql.append(" AND T2.DELFLAG =0");
			int _total = sjt.queryForInt(count_sql.append(sql).toString());
			List<SysUser> list = sjt.query(PageUtil.getPageSQL(query_sql.append(sql).toString(), pageIndex),
					new SysUserMapper());
			return new Pagination<SysUser>(list, _total, pageIndex);
		} else {
			StringBuilder sql_queryMainJob = new StringBuilder(
					"SELECT T1.NAME AS DEPTNAME,0 AS PARTTIME, T2.*,T2.DUTYNAME AS PARTTIMEDUTYNAME"
							+ " FROM T_SYS_DEPT T1, T_SYS_USER T2 "
							+ "WHERE T1.DEPTID = T2.DEPTID  AND T2.DELFLAG = 0 AND T2.DEPTID ='").append(deptid)
					.append("'");
			StringBuilder sql_queryPartTime = new StringBuilder("UNION ALL SELECT T4.NAME AS DEPTNAME,"
					+ "1 AS ISPARTTIME, T2.*,T6.DUTYNAME AS PARTTIMEDUTYNAME FROM T_SYS_PARTTIME T3,T_SYS_DEPT T4,"
					+ "T_SYS_USER T2 ,T_SYS_DUTY_DICT T6 WHERE T3.PARTTIMEDEPT =T4.DEPTID AND T3.USERID =T2.POLICENUM "
					+ "AND T3.DUTYID=T6.DUTYID " + "AND T3.PARTTIMEDEPT='").append(deptid).append("'");
			StringBuilder sql = new StringBuilder();
			StringBuilder sql_msg1 = new StringBuilder("SELECT COUNT(*) FROM (");
			StringBuilder sql_msg2 = new StringBuilder(")");
			if (StringUtil.isNotNull(policenum)) {
				sql.append(" AND T2.POLICENUM LIKE  UPPER('%" + policenum + "%')");
			}
			if (StringUtil.isNotNull(name)) {
				sql.append(" AND T2.NAME LIKE  '%" + name + "%'");
			}
			String sql_count = sql_msg1.append(sql_queryMainJob).append(sql).append(sql_queryPartTime).append(sql)
					.append(sql_msg2).toString();
			// System.out.println(sql_count);
			String querySql = "select * from ("
					+ sql_queryMainJob.append(sql).append(sql_queryPartTime).append(sql).toString()
					+ ") order by deptId,ordernum";
			// System.out.println(querySql);
			int _total = sjt.queryForInt(sql_count);
			List<SysUser> list = sjt.query(PageUtil.getPageSQL(querySql, pageIndex), new RowMapper<SysUser>() {
				@Override
				public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
					SysUser sysUser = new SysUser();
					SysDept sysDept = new SysDept();
					sysUser.setDbId(rs.getLong("DBID"));
					sysUser.setName(rs.getString("NAME"));
					sysUser.setIdCard(rs.getString("IDCARD"));
					sysUser.setDeptId(rs.getString("DEPTID"));
					sysUser.setPoliceNum(rs.getString("POLICENUM"));
					sysUser.setPassword(rs.getString("PASSWORD"));
					sysUser.setDutyName(rs.getString("DUTYNAME"));
					sysUser.setBirthday(rs.getString("BIRTHDAY"));
					sysUser.setRemark(rs.getString("REMARK"));
					sysUser.setActiveFlag(rs.getString("ACTIVEFLAG"));
					sysUser.setDeptName(rs.getString("DEPTNAME"));
					sysUser.setParttime(rs.getInt("PARTTIME"));
					sysUser.setPartTimedutyname(rs.getString("PARTTIMEDUTYNAME"));
					sysUser.setDelflag(rs.getString("DELFLAG"));
					sysDept.setName(rs.getString("DEPTNAME"));
//					sysUser.setSysDept(sysDept);
					return sysUser;
				}
			});
			return new Pagination<SysUser>(list, _total, pageIndex);
		}
	}

	@Override
	public Pagination<SysUser> queryUserInfoByPagination(String name, String deptname, String tel_mobile, int pageIndex) {
		StringBuilder query_sql = new StringBuilder(
				"SELECT T2.NAME AS DEPTNAME,T1.* FROM T_SYS_USER T1, T_SYS_DEPT T2 WHERE T1.DEPTID = T2.DEPTID");
		StringBuilder count_sql = new StringBuilder(
				"SELECT COUNT(*) FROM T_SYS_USER T1, T_SYS_DEPT T2 WHERE T1.DEPTID = T2.DEPTID");
		StringBuilder sql = new StringBuilder();
		if (!"".equals(name)) {
			sql.append(" AND T1.NAME LIKE '%" + name + "%'");
		}
		if (!"".equals(deptname)) {
			sql.append(" AND T2.NAME LIKE '%" + deptname + "%'");
		}
		if (!"".equals(tel_mobile)) {
			sql.append(" AND T1.TEL_MOBILE ='" + tel_mobile + "'");
		}
		sql.append(" AND T1.DELFLAG =0");
		int _total = sjt.queryForInt(count_sql.append(sql).toString());
		List<SysUser> list = sjt.query(PageUtil.getPageSQL(query_sql.append(sql).toString(), pageIndex),
				new SysUserMapper());
		return new Pagination<SysUser>(list, _total, pageIndex);
	}

	@Override
	public List<String[]> findRnameIdByUserApp(String policeNum, String appId) {
		String sql = "select r.rname,u.roleid from t_sys_user_role u,t_sys_role r where u.userid = ? and u.appid = ? and u.roleid = r.rcode";
		List<String[]> list = sjt.query(sql, new RowMapper<String[]>() {
			@Override
			public String[] mapRow(ResultSet rs, int arg1) throws SQLException {
				String[] role = new String[2];
				role[0] = rs.getString(2);
				role[1] = rs.getString(1);
				return role;
			}
		}, policeNum, appId);
		return list;
	}

	@Override
	public List<SysUser> findNameIdByDept(String deptId) {
		String sql = "select dbid,policenum,name from t_sys_user where deptid=? and delflag='0' order by policenum";
		List<SysUser> list = sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser user = new SysUser();
				user.setDbId(rs.getInt("dbid"));
				user.setPoliceNum(rs.getString("policeNum"));
				user.setName(rs.getString("name"));
				return user;
			}
		}, deptId);
		return list;
	}

	@Override
	public Pagination<SysUser> findUserContact(String deptId, int pageIndex, SysUser user, String inDept) {
		StringBuffer condition = new StringBuffer();
		if (user != null) {
			if (org.springframework.util.StringUtils.hasLength(user.getName())) {
				condition.append(" and u.name like '%" + user.getName() + "%'");
			}
			if (org.springframework.util.StringUtils.hasLength(user.getDutyName())) {
				condition.append(" and u.dutyName like '%").append(user.getDutyName()).append("%'");
			}
			if (org.springframework.util.StringUtils.hasLength(user.getPoliceNum())) {
				condition.append(" and u.policeNum like '%").append(user.getPoliceNum()).append("%'");
			}
		}
		if ("1".equals(inDept)) {
			// 部门内查询
			String sql_list = PageUtil
					.getPageSQL(
							"select u.name,u.dutyName,u.tel_office,u.deptId,u.tel_mobile,u.tel_short,u.tel_home,u.policeNum,d.Name deptName from t_sys_user u left join t_sys_dept d on u.deptId=d.deptId where u.delflag='0' and u.deptId=?"
									+ condition + " order by u.ordernum ", pageIndex);
			String sql_count = "select count(dbid) from t_sys_user u where deptId=? and delflag='0'" + condition;
			int _total = sjt.queryForInt(sql_count, deptId);
			List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
				@Override
				public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
					SysUser user = new SysUser();
					user.setName(rs.getString("name"));
					user.setDutyName(rs.getString("dutyName"));
					user.setPoliceNum(rs.getString("policeNum"));
					user.setDeptName(rs.getString("deptName"));
					user.setDeptId(rs.getString("deptId"));
					return user;
				}
			}, deptId);
			return new Pagination<SysUser>(list, _total, pageIndex);
		} else {
			String sql_list = PageUtil
					.getPageSQL(
							"select u.name,u.policeNum,u.dutyName,u.deptId,u.tel_office,u.tel_mobile,u.tel_short,u.tel_home,d.Name deptName  from t_sys_user u left join t_sys_dept d on u.deptId=d.deptId "
									+ condition.toString().replaceFirst("and", "where") + " order by u.ordernum ",
							pageIndex);
			String sql_count = "select count(dbid) from t_sys_user u"
					+ condition.toString().replaceFirst("and", "where");
			int _total = sjt.queryForInt(sql_count);
			List<SysUser> list = sjt.query(sql_list, new RowMapper<SysUser>() {
				@Override
				public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
					SysUser user = new SysUser();
					user.setName(rs.getString("name"));
					user.setDutyName(rs.getString("dutyName"));
					user.setPoliceNum(rs.getString("policeNum"));
					user.setDeptName(rs.getString("deptName"));
					user.setDeptId(rs.getString("deptId"));
					return user;
				}
			});
			return new Pagination<SysUser>(list, _total, pageIndex);
		}
	}

	@Override
	public List<SysUser> findAllUser() {
		String sql = "select policenum,deptId from t_sys_user where delflag='0'";
		return sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser user = new SysUser();
				user.setPoliceNum(rs.getString("policenum"));
				user.setDeptId(rs.getString("deptId"));
				return user;
			}
		});
	}

	@Override
	public List<SysUser> findAllUserDetails() {
		String sql = "select * from t_sys_user where delflag='0'";
		return sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser user = new SysUser();
				user.setPoliceNum(rs.getString("policenum"));
				user.setName(rs.getString("name"));
				user.setIdCard(rs.getString("idCard"));
				user.setDutyName(rs.getString("dutyName"));
				user.setActiveFlag(rs.getString("activeflag"));
				user.setDeptId(rs.getString("deptId"));
				return user;
			}
		});
	}

	@Override
	public SysUser findUserByPwdName(String loginId, String password, int idType) {
		String sql = "";
		switch (idType) {
		case 1:// 警号
			sql = "SELECT * FROM T_SYS_USER WHERE POLICENUM = ? AND PASSWORD = ?";
			break;
		case 2:// 身份证号
			sql = "SELECT * FROM T_SYS_USER WHERE IDCARD = ? AND PASSWORD = ?";
		}
		List<SysUser> list = sjt.query(sql, new SysUserMapper(true), loginId, password);
		if (!CollectionUtils.isEmpty(list)) {
			return list.get(0);
		}
		return null;
	}

	@Override
	public int findCountByIDCard(String idCard) {
		String sql = "select count(*) from t_sys_user where idcard=?";
		return sjt.queryForInt(sql, idCard);
	}

	@Override
	public int findCountByIDCard(String idCard, String policeNum) {
		String sql = "select count(*) from t_sys_user where idcard=? and policeNum!=?";
		return sjt.queryForInt(sql, idCard, policeNum);
	}

	@Override
	public int findMaxOrderNum(String deptId) {
		String sql = "select max(ordernum) from t_sys_user where deptId=?";
		return sjt.queryForInt(sql, deptId);
	}

	@Override
	public int findMinOrderNum(String deptId) {
		String sql = "select min(ordernum) from t_sys_user where deptId=?";
		return sjt.queryForInt(sql, deptId);
	}

	@Override
	public void updateOrdeDown(String policeNum, String deptId, int downOrder, int order, boolean hasDown) {
		if (hasDown) {
			String sql = "update t_sys_user set orderNum = ? where orderNum = ? and deptId=?";
			sjt.update(sql, order, downOrder, deptId);
			sjt.update("update t_sys_user set ordernum = ? where policeNum =?", downOrder, policeNum);
		}
	}

	@Override
	public void updateOrderToTop(String policeNum, String deptId, int minOrderNum, int orderNum) {
		String sql = "update t_sys_user set ordernum = ordernum+1 where ordernum <? and deptId=?";
		sjt.update(sql, orderNum, deptId);
		sjt.update("update t_sys_user set ordernum=? where policeNum = ?", minOrderNum, policeNum);
	}

	@Override
	public void updateOrderUp(String policeNum, String deptId, int upOrder, int order, boolean hasUp) {
		if (hasUp) {
			String sql = "update t_sys_user set orderNum = ? where orderNum = ? and deptId=?";
			sjt.update(sql, order, upOrder, deptId);
			sjt.update("update t_sys_user set ordernum = ? where policeNum =?", upOrder, policeNum);
		}
	}

	@Override
	public List<Integer> findUpDownOrder(int orderNum, String deptId) {
		String sql = "select ordernum from t_sys_user where deptId=? and delflag=0 order by ordernum";
		List<Integer> orders = sjt.query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getInt(1);
			}
		}, deptId);
		List<Integer> result = new ArrayList<Integer>();
		for (int i = 0; i < orders.size(); i++) {
			if (orders.get(i) == orderNum) {
				if (orders.size() == 0) {
					// 没有上一条、没有下一条
					result.add(0);
					result.add(0);
				} else if (i > 0 && i < orders.size() - 1) {
					// 有上一条、下一条
					result.add(orders.get(i - 1));
					result.add(orders.get(i + 1));
				} else if (i == 0) {
					// 有下一条而没有上一条
					result.add(0);
					result.add(orders.get(i + 1));
				} else {
					// 有上一条而没有下一条
					result.add(orders.get(i - 1));
					result.add(0);
				}
				break;
			}
		}
		return result;
	}

	@Override
	public List<SysUser> findUsersByAppRole(String appId, String roleId) {
		StringBuffer sql = new StringBuffer("select u.*,d.name deptName from t_sys_user u,t_sys_dept d");
		sql.append(" where exists").append("(select 1 from t_sys_user_role ur where roleid = ? and ")
				.append("u.policenum = ur.userid and exists (select 1 from t_sys_app_role")
				.append(" ar where ar.appid=? and ur.roleid=ar.roleid)) and u.deptId = d.deptId");
		return sjt.query(sql.toString(), new SysUserMapper(), roleId, appId);
	}

	@Override
	public List<SysUser> findPartTimes(String userId) {
		String sql = "select t2.userId,t2.deptId,t2.name,t1.dutyid,t1.dutyName from t_sys_parttime t1,t_sys_dept t2 where t1.parttimedept = t2.deptId and t1.userid=?";
		return sjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser user = new SysUser();
				user.setPoliceNum(rs.getString("userId"));
				user.setDeptName(rs.getString("deptName"));
				user.setDeptId(rs.getString("deptId"));
				user.setDutyId(rs.getString("dutyId"));
				user.setDutyName(rs.getString("dutyName"));
				return user;
			}
		}, userId);
	}

	@Override
	public Pagination<SysUser> findManagerUsers(String deptId, int pageIndex, SysUser user, String manageDept) {
		StringBuffer sql = new StringBuffer(
				"SELECT U.DBID,POLICENUM,U.NAME,D.NAME DEPTNAME,DUTYNAME,U.DEPTID FROM T_SYS_USER U LEFT ");
		sql.append("JOIN T_SYS_DEPT D ON U.DEPTID=D.DEPTID WHERE U.DEPTID LIKE '").append(manageDept).append("%'");
		if (org.springframework.util.StringUtils.hasLength(user.getName())) {
			sql.append(" AND U.NAME LIKE '%").append(user.getName()).append("%'");
		}
		if (org.springframework.util.StringUtils.hasLength(user.getPoliceNum())) {
			sql.append(" AND U.POLICENUM LIKE '%").append(user.getPoliceNum()).append("%'");
		}
		if (!org.springframework.util.StringUtils.hasLength(user.getName())
				&& !org.springframework.util.StringUtils.hasLength(user.getPoliceNum())) {
			sql.append(" AND U.deptId = '").append(deptId).append("'");
		}
		String count_sql = PageUtil.getCountSQL(sql.toString());
		String page_sql = PageUtil.getPageSQL(sql.toString(), pageIndex);
		List<SysUser> users = sjt.query(page_sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int arg1) throws SQLException {
				SysUser user = new SysUser();
				user.setPoliceNum(rs.getString("policenum"));
				user.setName(rs.getString("name"));
				user.setDeptName(rs.getString("deptName"));
				user.setDbId(rs.getLong("dbId"));
				user.setDeptId(rs.getString("deptId"));
				user.setDutyName(rs.getString("dutyName"));
				return user;
			}
		});
		int count = sjt.queryForInt(count_sql);
		return new Pagination<SysUser>(users, count, pageIndex);
	}

	@Override
	public Pagination<SysUser> findUserContacts(String deptId, String manageDept, int pageIndex, SysUser user) {
		StringBuffer sql = new StringBuffer("select u.*,d.name deptName from t_sys_user u left join t_sys_dept d");
		sql.append(" on u.deptId=d.deptId where u.delflag='0' and u.deptId like '").append(manageDept).append("%'");
		;
		if (org.springframework.util.StringUtils.hasLength(user.getPoliceNum())) {
			sql.append(" and u.policeNum like '").append(user.getPoliceNum()).append("%'");
		}
		if (org.springframework.util.StringUtils.hasLength(user.getName())) {
			sql.append(" and u.name like '").append(user.getName()).append("%'");
		}
		if (!org.springframework.util.StringUtils.hasLength(user.getPoliceNum())
				&& !org.springframework.util.StringUtils.hasLength(user.getName())) {
			sql.append(" and u.deptId='").append(deptId).append("'");
		}
		String count_sql = PageUtil.getCountSQL(sql.toString());
		String page_sql = PageUtil.getPageSQL(sql.append(" order by u.deptId,u.ordernum").toString(), pageIndex);
		List<SysUser> users = sjt.query(page_sql, new SysUserMapper());
		int count = sjt.queryForInt(count_sql);
		return new Pagination<SysUser>(users, count, pageIndex);
	}

	public void editPhone(SysUser user) {
	}

	@Override
	public List<String> findByParentId(String deptId) {
		while (deptId.endsWith("0")) {
			deptId = deptId.substring(0, deptId.length() - 1);
		}
		String sql = "select policeNum from t_sys_user where delflag='0' and deptId like '" + deptId + "%'";
		return sjt.query(sql, new RowMapper<String>() {
			@Override
			public String mapRow(ResultSet arg0, int arg1) throws SQLException {
				return arg0.getString("policeNum");
			}
		});
	}

	@Override
	public SysUser findUserByIdCard(String loginId) {
		String sql = "SELECT T1.NAME AS DEPTNAME,T2.* FROM T_SYS_DEPT T1, T_SYS_USER T2 WHERE T1.DEPTID = T2.DEPTID AND  T2.IDCARD= ?";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(), loginId);
		if (list.size() != 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	@Override
	public void resetPwd(String policeNum, String password) {
		String sql = "update t_sys_user set password=? where policeNum =?";
		sjt.update(sql, password, policeNum);
	}

	@Override
	public List<SysUser> findUserByFuzzyName(String userName, int maxRow) {
		return null;
		/*String sql = "SELECT V.* FROM(SELECT ROWNUM R,TRIM(T.NAME) NAME,T.POLICENUM,SUBSTR(T.IDCARD,7,8) BIRTHDAY,T.DUTYNAME FROM T_SYS_USER T WHERE T.NAME LIKE('"
				+ userName + "%')) V WHERE V.R <= " + maxRow;
		return extjt.query(sql, new RowMapper<SysUser>() {
			@Override
			public SysUser mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new SysUser(rs.getString("NAME"), rs.getString("POLICENUM"), rs.getString("DUTYNAME"), rs
						.getString("BIRTHDAY"));
			}
		});*/
	}

	@Override
	public SysUser _findUserByIdCard(String idCard) {
		String sql = "SELECT T1.NAME AS DEPTNAME,T2.* FROM T_SYS_DEPT T1, T_SYS_USER T2 WHERE T2.DELFLAG = '0' AND T1.DEPTID = T2.DEPTID AND T2.IDCARD= ?";
		List<SysUser> list = sjt.query(sql, new SysUserMapper(), idCard);
		if (list.size() != 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	@Override
	public List<SysUser> findUserInfoByXm(String xm, String deptId) {
		String SQL = "select t1.*,t2.alias deptName  from t_sys_user t1,t_sys_dept t2 where t1.deptid=t2.deptid and t1.name like '" + xm
				+ "%' and t1.policenum is not null ";
		if (StringUtil.isNotNull(deptId)) {
			SQL += "  and t1.deptid ='" + deptId + "'";
		}
		SQL += " order by t1.policenum";
		 System.out.println("--->"+SQL);
		List<SysUser> list = sjt.query(SQL,
				ParameterizedBeanPropertyRowMapper.newInstance(SysUser.class));
		return list;
	}
	
	@Override
	public List<SysUser> findAllUserInfoByXm(String xm) {
		String SQL = "select t1.*,t2.alias deptName  from t_sys_user t1,t_sys_dept t2 where t1.deptid=t2.deptid and t1.name like '" + xm
				+ "%' and t1.policenum is not null ";
		SQL += " order by t1.policenum";
		 System.out.println("--->"+SQL);
		List<SysUser> list = sjt.query(SQL,
				ParameterizedBeanPropertyRowMapper.newInstance(SysUser.class));
		return list;
	}
}
